import 'package:calf_account/database/schema.dart';
import 'package:calf_account/model/pass_tag.dart';
import 'package:calf_account/model/password.dart';
import 'package:calf_account/utils/date_util.dart';
import 'package:sqflite/sqflite.dart';

class CRUD {
  static const String _passTagTable = "passTags";
  static const String _passwordsTable = "passwords";
  static const String _passUnionTagTable = "passUnionTag";

  static final List<String> _tableSqls = [
    "CREATE TABLE $_passTagTable (id INTEGER PRIMARY KEY AUTOINCREMENT,"
        "name TEXT not null,"
        "updateTime TEXT not null)",
    "CREATE TABLE $_passwordsTable (id INTEGER PRIMARY KEY AUTOINCREMENT,"
        "useCount INTEGER not null,"
        "title TEXT not null,"
        "name TEXT not null,"
        "pass TEXT not null,"
        "note TEXT not null,"
        "website TEXT not null,"
        "updateTime TEXT not null)",
    "CREATE TABLE $_passUnionTagTable (id INTEGER PRIMARY KEY AUTOINCREMENT,"
        "pId INTEGER not null,"
        "tId INTEGER not null,"
        "updateTime TEXT not null)",
  ];

  static final CalfAccountSQL _database = CalfAccountSQL(_tableSqls);

  static void insertPassTag(PasswordTag tag) async {
    Database db = await _database.db;

    await db.insert(
      _passTagTable,
      {
        "name": tag.name,
        "updateTime": DateUtil.now(),
      },
      conflictAlgorithm: ConflictAlgorithm.replace,
    );

    db.close();
  }

  static void updatePassTag(PasswordTag tag) async {
    Database db = await _database.db;
    await db.update(
      _passTagTable,
      {
        "id": tag.id,
        "name": tag.name,
        "updateTime": DateUtil.now(),
      },
      where: "id = ?",
      whereArgs: [tag.id],
      conflictAlgorithm: ConflictAlgorithm.replace,
    );

    db.close();
  }

  static void insertPassword(Password ps) async {
    Database db = await _database.db;

    String time = DateUtil.now();

    int pId = await db.insert(
      _passwordsTable,
      {
        "useCount": ps.useCount,
        "title": ps.title,
        "name": ps.name,
        "pass": ps.pass,
        "note": ps.note,
        "website": ps.website,
        "updateTime": time,
      },
      conflictAlgorithm: ConflictAlgorithm.replace,
    );

    for (int tid in ps.tags) {
      await db.insert(
        _passUnionTagTable,
        {
          "pId": pId,
          "tId": tid,
          "updateTime": time,
        },
        conflictAlgorithm: ConflictAlgorithm.replace,
      );
    }

    db.close();
  }

  static void updatePassword(Password ps) async {
    Database db = await _database.db;

    String time = DateUtil.now();

    await db.update(
      _passwordsTable,
      {
        "useCount": ps.useCount,
        "title": ps.title,
        "name": ps.name,
        "pass": ps.pass,
        "note": ps.note,
        "website": ps.website,
        "updateTime": time,
      },
      where: "id = ?",
      whereArgs: [ps.id],
      conflictAlgorithm: ConflictAlgorithm.replace,
    );

    await db.delete(_passUnionTagTable, where: "pId = ?", whereArgs: [ps.id]);

    for (int tid in ps.tags) {
      await db.insert(
        _passUnionTagTable,
        {
          "pId": ps.id,
          "tId": tid,
          "updateTime": time,
        },
        conflictAlgorithm: ConflictAlgorithm.replace,
      );
    }

    db.close();
  }

  static Future<List<PasswordTag>> queryTags() async {
    List<PasswordTag> tags = [];
    Database db = await _database.db;
    List<Map<String, Object?>> list = await db.query(
      _passTagTable,
    );
    db.close();

    if (list.isNotEmpty) {
      for (Map map in list) {
        PasswordTag tag = PasswordTag();
        tag.id = map["id"];
        tag.name = map["name"];
        tag.updateTime = map["updateTime"];
        tags.add(tag);
      }
    }

    return tags;
  }

  static Future<List<Password>> queryPasswords(
      Iterable<int> seTagIds, String key) async {
    List<Password> results = [];
    Database db = await _database.db;
    List<Map<String, Object?>> ts = [];

    if (key.isNotEmpty) {
      ts = await db
          .query(_passwordsTable, where: "title LIKE ?", whereArgs: ["%$key%"]);
    } else {
      ts = await db.query(
        _passwordsTable,
      );
    }

    if (ts.isNotEmpty) {
      for (Map map in ts) {
        Password ps = Password();
        ps.id = map["id"];
        ps.useCount = map["useCount"];
        ps.title = map["title"];
        ps.name = map["name"];
        ps.pass = map["pass"];
        ps.note = map["note"];
        ps.website = map["website"];
        ps.updateTime = map["updateTime"];
        ps.tags = [];

        List<Map<String, Object?>> tagList = await db
            .query(_passUnionTagTable, where: "pId = ?", whereArgs: [ps.id]);
        if (tagList.isNotEmpty) {
          for (Map tMap in tagList) {
            ps.tags.add(tMap["tId"]);
          }
        }

        ///seIds
        if (!hasSeTag(seTagIds, ps)) {
          continue;
        }
        results.add(ps);
      }
    }

    db.close();
    return results;
  }

  static bool hasSeTag(Iterable<int> seTagIds, Password ps) {
    bool hasSe = false;
    if (seTagIds.isNotEmpty) {
      for (int seTagId in seTagIds) {
        if (ps.tags.contains(seTagId)) {
          hasSe = true;
          break;
        }
      }
    } else {
      hasSe = true;
    }
    return hasSe;
  }
}
